package de.tabiteuea.dbAccess;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Database manager class (MySQL)
 * Controls database access and saves/loads objects to/from database
 * @author Erik Schreiber
 */
public class DBManager {

	// Connection data
	// /TODO: logindaten mit gui verknuepfen
	String userName = "tabiteuea"; //user name
	String password = "tm_prak10"; //password
	String url = "jdbc:mysql://localhost/tabiteuea"; //database server url and database name
	Connection conn;

	/**
	 * Constructor connects to DB when called
	 */
	public DBManager() {

		this.connect();
	}

	/**
	 * Establish database connection
	 */
	private void connect() {

		this.conn = null;

		try {

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			this.conn = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");
		} catch (Exception e) {
			System.err.println("Cannot connect to database server: " + e);
		} finally {
			// nothing to do here
		}
	}

	/**
	 * Executes a query and returns a ResponseObject
	 * @param query query to be executed
	 * @return DBResponseObject consisting of tableName and a ResultSet
	 */
	private DBResponseObject query(String query) {

		Statement stmt = null; // SQL Statement	
		DBResponseObject response = new DBResponseObject();

		try {

			stmt = conn.createStatement();

			if (query.startsWith("SELECT")) { // SELECT-STATEMENTS

				ResultSet rs = stmt.executeQuery(query);
				
				//Get the table name which the query aims for
				String tableName = this.getTableName(query);
				
				response.setRs(rs);
				response.setTableName(tableName);								
				
			} else { // CREATE/DROP/INSERT/UPDATE/DELETE-STATEMENTS

				stmt.execute(query);
			}
		} catch (SQLException e) {
			System.err.println("Error while querying (query): " + query);
			System.err.println("Error: " + e);
			this.close();
		} finally {
			// do nothing
		}

		return response;
	}

	/**
	 * Extracts the table name from a given query
	 * @param query query string
	 * @return table name to which the query is adressed to
	 */
	private String getTableName(String query){
		
		String searchString = query.toUpperCase();
		
		//cut off the front-end
		int fromTag = query.indexOf("FROM");
		searchString = searchString.substring(fromTag + 5, searchString.length());
		searchString = searchString.trim();
		
		// cut off the back-end (if there is one)
		if (searchString.indexOf(" ") > 0) { 
			int spaceTag = searchString.indexOf(" ");
			searchString = searchString.substring(0, spaceTag);
		}		
		String tableName = searchString.trim(); // just in case
		
		return tableName;
		
	}
	
	/**
	 * Converts DBResponseObjects into a List of Objects, which represent the related database table
	 * @param response The Response Object, which will be converted
	 * @return An ArrayList consisting of DatabaseObjects. The exact object type depends on the related database table.
	 */
	private List<Object> generateResultList(DBResponseObject response){
		
		List<Object> results = new ArrayList<Object>(); // list of ResultSets
		
		ResultSet rs = response.getRs();
		String tableName = response.getTableName(); 
			
		try{
		
			while (rs.next()) {
	
				if (tableName.equals("ANALYSIS")) {
	
					AnalysisObject queryObject = new AnalysisObject();
	
					queryObject.setId(rs.getInt("id"));
					queryObject.setCorpus_id(rs.getInt("corpus_id"));
					queryObject.setSentence_length(rs.getDouble("sentence_length"));
					queryObject.setSignificance_type(rs.getInt("significance_type"));
					queryObject
							.setWord_coverage(rs.getInt("word_coverage"));
	
					results.add(queryObject);
				} else if (tableName.equals("COOCCURRENCES")) {
	
					CooccurrenceObject queryObject = new CooccurrenceObject();
	
					queryObject.setId(rs.getInt("id"));
					queryObject.setAnalysis_id(rs.getInt("analysis_id"));					
					queryObject.setWord1(rs.getString("word1"));
					queryObject.setWord2(rs.getString("word1"));
					queryObject.setSignificance(rs.getFloat("significance"));
	
					results.add(queryObject);
	
				} else if (tableName.equals("CORPUS")) {
	
					CorpusObject queryObject = new CorpusObject();
	
					queryObject.setId(rs.getInt("id"));
					queryObject.setText(rs.getString("text"));
	
					results.add(queryObject);
				} else if (tableName.equals("FREQUENCIES")) {
	
					FrequencyObject queryObject = new FrequencyObject();
	
					queryObject.setId(rs.getInt("id"));
					queryObject.setCorpus_id(rs.getInt("corpus_id"));
					queryObject.setWord(rs.getString("word"));
					queryObject.setFrequency(rs.getInt("frequency"));
	
					results.add(queryObject);
	
				} else {
					System.out.println("ERROR: Unknown table: " + tableName);
				}
			}//END WHILE
			
		} catch (SQLException e) {
			System.err.println("Error while querying (generateResultList): " + e);
			this.close();
		} finally {
			// do nothing
		}
		
		return results;
		
	}
	
	/**
	 * Creates required database tables
	 */
	public void createTables() {

		String query = null;	

		// table corpus
		query = "CREATE TABLE corpus (id INT UNSIGNED NOT NULL AUTO_INCREMENT, text LONGBLOB NULL, PRIMARY KEY ( id )) ENGINE = MYISAM";
		this.query(query);

		// table analysis
		query = "CREATE TABLE analysis (id INT UNSIGNED NOT NULL AUTO_INCREMENT, corpus_id INT UNSIGNED NOT NULL, significance_type INT UNSIGNED NOT NULL, sentence_length DOUBLE NULL, word_coverage INT UNSIGNED NULL, PRIMARY KEY ( id )) ENGINE = MYISAM";
		this.query(query);

		// table frequencies
		query = "CREATE TABLE frequencies (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, corpus_id INT UNSIGNED NOT NULL, word VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, frequency INT UNSIGNED NOT NULL) ENGINE = MYISAM";
		this.query(query);

		// table cooccurrences
		query = "CREATE TABLE cooccurrences (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, analysis_id INT UNSIGNED NOT NULL, word1 VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, word2 VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, significance FLOAT NOT NULL) ENGINE = MYISAM";
		this.query(query);	
	}
	
	/**
	 * Saves a single corpus string to the database 
	 * @param corpusValue the whole corpus as a string
	 * @return id which was assigned to the inserted corpus by the db
	 */
	public int addCorpus(String corpusValue) {

		int corpus_id = 0;
		
		String query = "INSERT INTO corpus (text) VALUES (\"" + corpusValue + "\")";
		this.query(query);
				
		query = "SELECT id FROM corpus ORDER BY id DESC LIMIT 1";
		DBResponseObject response = this.query(query);
		ResultSet rs = response.getRs();
		
		try{
			rs.next();
			corpus_id = rs.getInt("id");
			
		} catch (SQLException e) {
			System.err.println("Error while querying (addCorpus): " + query);
			System.err.println("Error: " + e);
			this.close();
		} finally {
			// do nothing
		}
		
		return corpus_id;
	}
	
	/**
	 * Saves a single corpus object to the database 
	 * @param corp the whole corpus as an object
	 * @return id which was assigned to the inserted corpus by the db
	 */
	public int addCorpus(CorpusObject corp) {

		int corpus_id = 0;
		
		String corpusValue = corp.getText();
		
		String query = "INSERT INTO corpus (text) VALUES (\"" + corpusValue + "\")";
		this.query(query);
				
		query = "SELECT id FROM corpus ORDER BY id DESC LIMIT 1";
		DBResponseObject response = this.query(query);
		ResultSet rs = response.getRs();
		
		try{
			rs.next();
			corpus_id = rs.getInt("id");
			
		} catch (SQLException e) {
			System.err.println("Error while querying (addCorpus): " + query);
			System.err.println("Error: " + e);
			this.close();
		} finally {
			// do nothing
		}
		
		return corpus_id;
	}

	/**
	 * Retrieves a corpus as object from the database 
	 * @param id the id of the corpus in the database
	 * @return CorpusObject containing the database id an the actual corpus
	 */
	public CorpusObject getCorpusObject(int id) {

		String sql = "SELECT * FROM corpus WHERE id = " + id;
		DBResponseObject response = this.query(sql);
		List<Object> results = generateResultList(response);
		CorpusObject corp = null;
				
		Object obj = results.get(0); //There will be just one element
		
		if (obj instanceof CorpusObject) {			
			corp = (CorpusObject)obj;
		}		

		return corp;
	}

	/**
	 * Retrieves a corpus as string from the database 
	 * @param id the id of the corpus in the database
	 * @return String containing the the actual corpus
	 */
	public String getCorpusText(int id) {

		String sql = "SELECT * FROM corpus WHERE id = " + id;
		DBResponseObject response = this.query(sql);
		List<Object> results = generateResultList(response);
		CorpusObject corp = null;
		String corpValue = null;
				
		Object obj = results.get(0); //There will be just one element
		
		if (obj instanceof CorpusObject) {			
			corp = (CorpusObject)obj;
			corpValue = corp.getText();
		}		

		return corpValue;
	}
		
	/**
	 * Saves an analysis object to database 
	 * @param analysis Object containing all analysis data
	 * @return id which was assigned to the inserted analysis by the db
	 */
	public int addAnalysisObject(AnalysisObject analysis) {

		int analysis_id = 0;
		
		String query = "INSERT INTO analysis (" +
							"corpus_id, " +
							"significance_type, " +
							"sentence_length, " +
							"word_coverage" +
						") VALUES (" + 
							analysis.getCorpus_id() + "," +
							analysis.getSignificance_type() + "," +
							analysis.getSentence_length() + "," +
							analysis.getWord_coverage() +
						")";
		this.query(query);
				
		query = "SELECT id FROM analysis ORDER BY id DESC LIMIT 1";
		DBResponseObject response = this.query(query);
		ResultSet rs = response.getRs();
		
		try{
			rs.next();
			analysis_id = rs.getInt("id");
			
		} catch (SQLException e) {
			System.err.println("Error while querying (addAnalysisObject): " + query);
			System.err.println("Error: " + e);
			this.close();
		} finally {
			// do nothing
		}
		
		return analysis_id;
	}

	/**
	 * Saves an list of analysis objects to the database 
	 * @param anaList List containing all analysisObjects to be saved	 
	 */
	public void addAnalysisList(List<AnalysisObject> anaList) {

		for (int i=0; i<anaList.size(); i++){
			
			AnalysisObject analysis = anaList.get(i);
		
			String query = "INSERT INTO analysis (" +
								"corpus_id, " +
								"significance_type, " +
								"sentence_length, " +
								"word_coverage" +
							") VALUES (" + 
								analysis.getCorpus_id() + "," +
								analysis.getSignificance_type() + "," +
								analysis.getSentence_length() + "," +
								analysis.getWord_coverage() +
							")";
			this.query(query);
		}
	}
	
	/**
	 * Retrieves all analysis data belonging to a specified corpus 
	 * @param corpus_id the id of the corpus in the database
	 * @return List consisting of all related analysis objects
	 */
	public List<AnalysisObject> getAnalysisList(int corpus_id) {

		String sql = "SELECT * FROM analysis WHERE corpus_id = " + corpus_id;
		DBResponseObject response = this.query(sql);
		List<Object> results = generateResultList(response);
		List<AnalysisObject> anaList = new ArrayList<AnalysisObject>();
		
		for (int i=0; i<results.size(); i++){
		
			Object obj = results.get(i);
			
			if (obj instanceof AnalysisObject) {			
				
				AnalysisObject ana = (AnalysisObject)obj;
				anaList.add(ana);				
			}			
		}		
		return anaList;
	}

	/**
	 * Retrieves a specified analysis object from the db 
	 * @param id the id of the analysis object in the database
	 * @return AnalysisObject containing all analysis data
	 */
	public AnalysisObject getAnalysisObject(int id) {

		String sql = "SELECT * FROM analysis WHERE id = " + id;
		DBResponseObject response = this.query(sql);
		List<Object> results = generateResultList(response);
						
		Object obj = results.get(0); //Only one result possible
		AnalysisObject ana = null;
		
		if (obj instanceof AnalysisObject) {		
				
			ana = (AnalysisObject)obj;							
		}			
				
		return ana;
	}
	
	/**
	 * Saves an list of cooccurrence objects to the database 
	 * @param coocList List containing all cooccurrenceObjects to be saved	 
	 */
	public void addCooccurrenceList(List<CooccurrenceObject> coocList) {

		for (int i=0; i<coocList.size(); i++){
			
			CooccurrenceObject cooc = coocList.get(i);
		
			String query = "INSERT INTO cooccurrences (" +
								"analysis_id, " +
								"word1, " +
								"word2, " +
								"significance" +
							") VALUES (" + 
								cooc.getAnalysis_id() + ",\"" +
								cooc.getWord1() + "\",\"" +
								cooc.getWord2() + "\"," +
								cooc.getSignificance() +
							")";
			this.query(query);
		}
	}

	/**
	 * Loads a List containing all cooccurrences from a specified analysis
	 * @param analysis_id id of the related analysis in the database
	 * @return all cooccurrences of that corpus
	 */
	public List<CooccurrenceObject> getCooccurrences(int analysis_id) {

		String sql = "SELECT * FROM cooccurrences WHERE analysis_id = " + analysis_id;
		DBResponseObject response = this.query(sql);
		List<Object> results = generateResultList(response);
		List<CooccurrenceObject> cooccurrences = new ArrayList<CooccurrenceObject>();
		
		for (int i=0; i<results.size(); i++){
		
			Object o = results.get(i);
		
			if (o instanceof CooccurrenceObject) {			
				cooccurrences.add((CooccurrenceObject) o);
			}
		}
		
		return cooccurrences;
	}

	/**
	 * Saves an list of frequency objects to the database 
	 * @param freqList List containing all frequencyObjects to be saved
	 */
	public void addFrequency(List<FrequencyObject> freqList) {
		
		for (int i=0; i<freqList.size(); i++){
			
			FrequencyObject freq = freqList.get(i);
			
			// due database has VarChar(256) as type, prevent longer words
			if (freq.getWord().length() >= 256) {
				System.out.println("Error: word longer than 256 chars: " + freq.getWord() + ")");
			} else {
		
				String query = "INSERT INTO frequencies (" +
									"corpus_id, " +
									"word, " +
									"frequency" +
								") VALUES (" + 
									freq.getCorpus_id() + ",\"" +
									freq.getWord() + "\"," +
									freq.getFrequency() +								
								")";
				this.query(query);
			}			
		}
	}

	/**
	 * Loads a List containing all frequencies from a specified corpus
	 * @param corpus_id id of the related corpus in the database
	 * @return all frequencies of that corpus as list of objects
	 */
	public List<FrequencyObject> getFrequencyList(int corpus_id) {

		String sql = "SELECT * FROM frequencies WHERE corpus_id = " + corpus_id;
		DBResponseObject response = this.query(sql);
		List<Object> results = generateResultList(response);
		List<FrequencyObject> frequencies = new ArrayList<FrequencyObject>();
		
		for (int i=0; i<results.size(); i++){
		
			Object o = results.get(i);
		
			if (o instanceof FrequencyObject) {			
				frequencies.add((FrequencyObject) o);
			}
		}
		
		return frequencies;
	}

	/**
	 * delete all data from database tables
	 */
	public void clearTables() {
		
		String clearCorpus = "TRUNCATE TABLE corpus";
		String clearAnalysis = "TRUNCATE TABLE analysis";
		String clearCooccurrences = "TRUNCATE TABLE cooccurrences";
		String clearFrequencies = "TRUNCATE TABLE frequencies";
		
		this.query(clearCorpus);
		this.query(clearAnalysis);
		this.query(clearCooccurrences);
		this.query(clearFrequencies);
	}
		
	/**
	 * press the big red button 
	 */
	public void deleteTables() {
		
		this.clearTables();
		
		String clearCorpus = "DROP TABLE corpus";
		String clearAnalysis = "DROP TABLE analysis";
		String clearCooccurrences = "DROP TABLE cooccurrences";
		String clearFrequencies = "DROP TABLE frequencies";
		
		this.query(clearCorpus);
		this.query(clearAnalysis);
		this.query(clearCooccurrences);
		this.query(clearFrequencies);
		
	}

	/**
	 * closes the database connection
	 */
	public void close() {
		if (this.conn != null) {
			try {
				conn.close();
				System.out.println("Database connection closed");

			} catch (Exception e) {
				System.err.println("Error while closing connection: " + e);
			}

		} else {
			System.out.println("No connection to close");
		}
	}
}
